package it.univaq.disim.mwt.business.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import it.univaq.disim.mwt.business.LaboratoriService;
import it.univaq.disim.mwt.business.model.Insegnante;
import it.univaq.disim.mwt.business.model.Laboratorio;
import it.univaq.disim.mwt.business.model.TipologiaLaboratorio;

public class JDBCLaboratorioService implements LaboratoriService{
	
	//FUNZIONA
	@Override
	public Laboratorio findLaboratorioById(Long id) {
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		String sql = "select * from laboratori where id = ? ";
		Laboratorio laboratori = null;
		try {
			connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "school", "school");
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setLong(1, id);
			preparedStatement.executeQuery();				
			resultSet = preparedStatement.getResultSet();
			resultSet.next();
			
			Long ide = resultSet.getLong("id");
			String nome = resultSet.getString("nome");
			Long tipologiaLaboratorio = resultSet.getLong("id_tipologia_laboratorio");
			Long idResponsabile = resultSet.getLong("id_responsabile");
			
			JDBCTipologiaLaboratoriService jdbcTipologiaLaboratoriService= new JDBCTipologiaLaboratoriService();
			TipologiaLaboratorio tipologiaLaboratori=jdbcTipologiaLaboratoriService.findById(tipologiaLaboratorio);
			
			JDBCInsegnanteService jdbcInsegnanteService= new JDBCInsegnanteService();
			Insegnante insegnanti=jdbcInsegnanteService.findInsegnanteById(idResponsabile);
			
			laboratori = new Laboratorio(ide, nome,tipologiaLaboratori ,insegnanti);
			
			resultSet.close();
			preparedStatement.close();
			connection.close();
			
			return laboratori ;

		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}

	//FUNZIONA
	@Override
	public List<Laboratorio> findAllLaboratori() {
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		List<Laboratorio> listLab = new ArrayList<Laboratorio>();
		String sql = " select * from laboratori ";
		Laboratorio laboratori = null;
		
		try {
			connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "school", "school");
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.executeQuery();	
			resultSet = preparedStatement.getResultSet();
		
			while(resultSet.next()){
				

				Long ide = resultSet.getLong("id");
				String nome = resultSet.getString("nome");
				Long tipologiaLaboratorio = resultSet.getLong("id_tipologia_laboratorio");
				Long idResponsabile = resultSet.getLong("id_responsabile");
				
				JDBCTipologiaLaboratoriService jdbcTipologiaLaboratoriService= new JDBCTipologiaLaboratoriService();
				TipologiaLaboratorio tipologiaLaboratori=jdbcTipologiaLaboratoriService.findById(tipologiaLaboratorio);
				
				JDBCInsegnanteService jdbcInsegnanteService= new JDBCInsegnanteService();
				Insegnante insegnanti=jdbcInsegnanteService.findInsegnanteById(idResponsabile);
				
				laboratori = new Laboratorio(ide, nome,tipologiaLaboratori ,insegnanti);
				listLab.add(laboratori);
			}
			
			resultSet.close();
			preparedStatement.close();
			connection.close();
			
			return listLab;
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}

}